package dao;

import po.Book;
import po.Order;
import utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

public class BookDao {
    /**
     * 查询出所有的图书数据
     */
    public ArrayList<Book> selectAll(){
        Connection connection = DBUtils.getConn();
        ArrayList<Book> list = new ArrayList<Book>();
        Book book = null;
        String sql ="select * from tb_book";
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
           resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                book = new Book();
                book.setAuthor(resultSet.getString("author"));
                book.setEdition(resultSet.getInt("edition"));
                book.setForm(resultSet.getString("form"));
                book.setFormat(resultSet.getString("format"));
                book.setIsbn(resultSet.getString("isbn"));
                book.setPackaging(resultSet.getString("packaging"));
                book.setPages(resultSet.getInt("pages"));
                book.setPress(resultSet.getString("press"));
                book.setPrice(resultSet.getDouble("price"));
                book.setPublished(resultSet.getDate("published"));
                book.setTitle(resultSet.getString("title"));
                book.setWords(resultSet.getInt("words"));
                list.add(book);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }
    /**
     * 根据isbn查询数据
     */
    public Book selectByIsbn(Book book){
        Book b= null;
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql ="select * from tb_book where isbn=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,book.getIsbn());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                b = new Book();
                b.setAuthor(resultSet.getString("author"));
                b.setEdition(resultSet.getInt("edition"));
                b.setForm(resultSet.getString("form"));
                b.setFormat(resultSet.getString("format"));
                b.setIsbn(resultSet.getString("isbn"));
                b.setPackaging(resultSet.getString("packaging"));
                b.setPages(resultSet.getInt("pages"));
                b.setPress(resultSet.getString("press"));
                b.setPrice(resultSet.getDouble("price"));
                b.setPublished(resultSet.getDate("published"));
                b.setTitle(resultSet.getString("title"));
                b.setWords(resultSet.getInt("words"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return b;
    }

    public ArrayList<Book> findByTitle(String title) {
        Book b = null;
        ArrayList<Book> list = new ArrayList<Book>();
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from tb_book where title like '%"+title+"%'";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                b = new Book();
                b.setAuthor(resultSet.getString("author"));
                b.setEdition(resultSet.getInt("edition"));
                b.setForm(resultSet.getString("form"));
                b.setFormat(resultSet.getString("format"));
                b.setIsbn(resultSet.getString("isbn"));
                b.setPackaging(resultSet.getString("packaging"));
                b.setPages(resultSet.getInt("pages"));
                b.setPress(resultSet.getString("press"));
                b.setPrice(resultSet.getDouble("price"));
                b.setPublished(resultSet.getDate("published"));
                b.setTitle(resultSet.getString("title"));
                b.setWords(resultSet.getInt("words"));
                list.add(b);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public int selectCount() {
        int totalCount = 0;
        Connection connection = DBUtils.getConn();
        String sql = "select count(*) from tb_book";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return totalCount;
    }

    public ArrayList<Book> selectAllPaged(int start, int length) {
        ArrayList<Book> list = new ArrayList<Book>();
        Book book = null;
        Connection connection = DBUtils.getConn();
        String sql = "select * from tb_book order by isbn desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,start);
            preparedStatement.setInt(2,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                book = new Book();
                book.setAuthor(resultSet.getString("author"));
                book.setEdition(resultSet.getInt("edition"));
                book.setForm(resultSet.getString("form"));
                book.setFormat(resultSet.getString("format"));
                book.setIsbn(resultSet.getString("isbn"));
                book.setPackaging(resultSet.getString("packaging"));
                book.setPages(resultSet.getInt("pages"));
                book.setPress(resultSet.getString("press"));
                book.setPrice(resultSet.getDouble("price"));
                book.setPublished(resultSet.getTimestamp("published"));
                book.setTitle(resultSet.getString("title"));
                book.setWords(resultSet.getInt("words"));
                list.add(book);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public Integer updatePriceById(Book book) {
        Connection connection =DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        Integer row = null;
        String sql = "update tb_book set price=? where isbn=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1,book.getPrice());
            preparedStatement.setString(2,book.getIsbn());
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;
    }

    public Integer insert(Book book) {
        Integer row = null;
        Connection connection =DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        String sql = "insert into tb_book values (?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,book.getIsbn());
            preparedStatement.setString(2,book.getTitle());
            preparedStatement.setString(3,book.getAuthor());
            preparedStatement.setDouble(4,book.getPrice());
            preparedStatement.setString(5,book.getPress());
            preparedStatement.setInt(6,book.getEdition());
            preparedStatement.setDate(7,null);
            preparedStatement.setInt(8,book.getPages());
            preparedStatement.setInt(9,book.getWords());
            preparedStatement.setString(10,book.getPackaging());
            preparedStatement.setString(11,book.getFormat());
            preparedStatement.setString(12,book.getForm());
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;
    }
}
